package com.pinusmusic.dao.impl;

import com.pinusmusic.dao.MusicListDao;
import com.pinusmusic.domain.MusicList;
import com.pinusmusic.domain.Singer;
import com.pinusmusic.domain.User;
import com.pinusmusic.util.JDBCUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

public class MusicListDaoImpl implements MusicListDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public void addMusicList(MusicList addMusicList) {
        String sql = "insert into musiclist(musiclistname, musiclistimg) values (?,?)";
        template.update(sql, addMusicList.getMusiclistname(), addMusicList.getMusiclistimg());
    }

    @Override
    public void deleteMusicListById(int mlid) {
        String sql = "delete from musiclist where mlid=?";
        template.update(sql, mlid);
    }

    @Override
    public boolean updateMusicList(MusicList updateMusicList) {
        String sql = "update musiclist set musiclistname=?,musiclistimg=? where mlid=?";
        int i = template.update(sql, updateMusicList.getMusiclistname(),
                updateMusicList.getMusiclistimg(), updateMusicList.getMlid());
        return i != 0;
    }

    @Override
    public MusicList findMusicListByMlid(int mlid) {
        String sql = "select * from musiclist where mlid=?";
        return template.queryForObject(sql, new BeanPropertyRowMapper<>(MusicList.class), mlid);
    }

    @Override
    public int getTotalCount() {
        String sql = "select count(*) from musiclist";
        return template.queryForObject(sql, Integer.class);
    }

    @Override
    public List<MusicList> findMusicListByPage(int start, int rows) {
        String sql = "select * from musiclist limit ?,?";
        return template.query(sql, new BeanPropertyRowMapper<>(MusicList.class), start, rows);
    }

    @Override
    public String findPathByMlid(Integer mlid) {
        String sql = "select musiclistimg from musiclist where mlid=?";
        String imgPath = null;
        try {
            imgPath = template.queryForObject(sql, String.class, mlid);
        } catch (EmptyResultDataAccessException e) {
            System.out.println("路径查询失败");//如果查询结果为空或多个，则返回-1
        }
        return imgPath;
    }

    @Override
    public int getCountByUid(int uid) {
        String sql = "select count(*) from musiclist where createrid=?";
        return template.queryForObject(sql, Integer.class, uid);
    }

    @Override
    public List<MusicList> findMusicListsByName(int start, int rows, String name) {
        if (name != null && !name.trim().isEmpty() && !name.equals("")) {
            String sql = "select * from musiclist where 1=0";
            StringBuilder sb = new StringBuilder(sql);
            ArrayList<Object> params = new ArrayList<>();//存放musicName的值
            sb.append(" or musiclistname like ?");
            params.add("%" + name + "%");
            //分页
            sb.append("limit ?,?");
            params.add(start);
            params.add(rows);
            List<MusicList> lists = template.query(sb.toString(), new BeanPropertyRowMapper<>(MusicList.class), params.toArray());
            return lists.size() == 0 ? null : lists;
        }
        return null;
    }

    @Override
    public int getLikeCount(String name) {
        String sql = "select count(*) from musiclist  where 1=0";
        StringBuilder sb = new StringBuilder(sql);
        ArrayList<Object> params = new ArrayList<>();//存放musicName的值
        if (name != null && !name.trim().isEmpty()) {
            sb.append(" or musiclistname like ?");
            params.add("%" + name + "%");
        }
        return template.queryForObject(sb.toString(), Integer.class, params.toArray());
    }

    //2.0
    @Override
    public int findCreatorIdByMlid(Integer mlid) {
        String sql = "select createrid from musiclist where mlid=?";
        return template.queryForObject(sql, Integer.class, mlid);
    }

    @Override
    public List<MusicList> findCreationlistByUid(Integer uid) {
        String sql = "select * from musiclist where createrid=?";
        return template.query(sql, new BeanPropertyRowMapper<>(MusicList.class), uid);
    }

    @Override
    public List<MusicList> findCollectedlistByUid(int uid) {
        String sql1 = "select mlid from collect where uid=?";
        String sql2 = "select * from musiclist where mlid=?";
        String sql3 = "select createrid from musiclist where mlid=?";
        List<Integer> mlidlist = template.queryForList(sql1, Integer.class, uid);
        System.out.println("mlidlist:" + mlidlist);
        List<MusicList> musicLists = new ArrayList<>();
        for (int mlid : mlidlist) {
            MusicList musicList = template.queryForObject(sql2, new BeanPropertyRowMapper<>(MusicList.class), mlid);
            User creator = new User();
            creator.setUid(template.queryForList(sql3, Integer.class, mlid).get(0));
            musicList.setUser(creator);
            musicLists.add(musicList);
        }

        return musicLists;
    }

    @Override
    public void cancelCollectByMlid(int mlid, int uid) {
        String sql = "delete from collect where mlid=? and uid=?";
        template.update(sql, mlid, uid);
    }

    @Override
    public boolean isExistByMusiclistnameAndUid(String musiclistname, Integer uid) {
        String sql = "select count(*) from musiclist where musiclistname=? and createrid=?";
        Integer count = template.queryForObject(sql, Integer.class, musiclistname, uid);
        return count==0;
    }

    @Override
    public void createList(String musiclistname, Integer uid) {
        String sql = "insert into musiclist(musiclistname, createrid) values (?,?)";
        template.update(sql, musiclistname, uid);
    }

    @Override
    public MusicList findMusicListByMusiclistName(String musiclistname,int uid) {
        String sql = "select * from musiclist where musiclistname=? and createrid=?";
        return template.queryForObject(sql, new BeanPropertyRowMapper<>(MusicList.class), musiclistname, uid);
    }

    @Override
    public void collectByMlid(int mlid, int uid) {
        String sql = "insert into collect(mlid,uid) values (?,?)";
        template.update(sql, mlid, uid);
    }

    @Override
    public boolean isCollected(int mlid, int uid) {
        String sql = "select collectid from collect where mlid=? and uid=?";
        try{
            template.queryForObject(sql, Integer.class, mlid, uid);
            return true;
        }catch(EmptyResultDataAccessException e){
            return false;
        }catch(IncorrectResultSizeDataAccessException e){
            System.out.println("查询到多条");
            return true;
        }
    }
    //lwj


}
